package com.willie.utils.sql;

import com.willie.utils.sql.table.Table;
import com.willie.utils.string.StringUtil;

import java.util.*;

/**
 * ----------------------------------------------
 * <p>类    名：</p>
 * <p>版    权：copyright© firegy.willie</p>
 * <p>作    者: liang</p>
 * <p>创建时间: 2018/6/2 15:52</p>
 * <p>描    述: sql工具类用于拼接sql</p>
 * <p>修 改 人：</p>
 * <p>修改时间：</p>
 * -----------------------------------------------
 */
public abstract class SqlUtil {
    public static final String UPDATE_ACTION_TABLENAME = "sync_trigger_log";//记录更新操作表名

    /**
     * 构建建表sql
     *
     * @param table 表
     * @return 建表sql
     */
    public static String buildCreateTableSql(Table table) {
        if (Objects.isNull(table)) {
            throw new RuntimeException("数据表不存在");
        }
        String tableName = table.getName();
        String pk = table.getPrimaryKey();
        List<Table.Field> fields = table.getFields();

        if (Objects.isNull(fields) || fields.isEmpty()) {
            return null;
        }

        String columnName;
        String columnType;

        StringBuilder sql = new StringBuilder("CREATE TABLE ");
        sql.append(tableName).append("(");

        String dataTypeSql;
        for (Table.Field field : fields) {
            columnName = field.getName();
            columnType = field.getType();
            int precision = field.getPrecision();
            int scale = field.getScale();
            boolean nullable = field.isNullable();

            //拼接sql
            sql.append(columnName).append(" ");
            dataTypeSql = SqlUtil.buildDataTypeSql(columnType, precision, scale);
            sql.append(dataTypeSql);

            if (!nullable) {
                sql.append("NOT NULL");
            }

            sql.append(",");
        }

        sql.append(" CONSTRAINT PK_").append(pk).append(" PRIMARY KEY(").append(pk).append(")").append(")");
        return sql.toString();
    }

    /**
     * 根据字段数据类型构造sql
     *
     * @param columnType 子弹类型
     * @return 数据类型sql
     */
    private static String buildDataTypeSql(String columnType, int precision, int scale) {
        StringBuilder dataTypeSql = new StringBuilder();
        dataTypeSql.append(columnType);

        //数值类型目前只按小数和整数讨论
        if ("NUMBER".equals(columnType)) {
            if (scale > 0) {//小数
                dataTypeSql.append("(").append(precision).append(",").append(scale).append(")").append(" ");
            } else {//整数
                dataTypeSql.append("(").append(precision).append(")").append(" ");
            }
        }

        //字符型
        if ("VARCHAR2".equals(columnType) || "CHAR".equals(columnType)) {
            dataTypeSql.append("(").append(precision).append(")").append(" ");
        }

        return dataTypeSql.toString();
    }

    /**
     * 构建批量插入sql
     *
     * @param tableName 表名
     * @param rowList   记录
     * @return 批量插入sql
     */
    public static String buildBatchInsertSql(String tableName, List<Map<String, String[]>> rowList) {
        if (Objects.isNull(rowList) || rowList.isEmpty()) {
            return null;
        }

        Set<String> columnKeys = rowList.get(0).keySet();//所有的列
        String columns = StringUtil.concatWithCommas(columnKeys);//列sql

        Collection<String[]> columnValues;//所有列的值
        String valuesSql;//值sql
        StringBuilder batchInsertSql = new StringBuilder(" INSERT ALL ");
        for (Map<String, String[]> rowMap : rowList) {
            columnValues = rowMap.values();//所有列的值
            valuesSql = SqlUtil.buildInsertValuesSql(columnValues);//构建列的值sql
            batchInsertSql.append(" INTO ").append(tableName).append("(").append(columns).append(")").append(" VALUES(").append(valuesSql).append(")");
        }
        batchInsertSql.append(" select 1 from dual ");
        return batchInsertSql.toString();
    }

    /**
     * 构建插入值的sql
     *
     * @param columnValues 值
     * @return 插入值的sql
     */
    private static String buildInsertValuesSql(Collection<String[]> columnValues) {
        if (Objects.isNull(columnValues) || columnValues.isEmpty()) {
            return null;
        }

        StringBuilder valueSql = new StringBuilder();
        Iterator it = columnValues.iterator();
        String[] columnInfo;
        String columnType;
        String columnValue;
        while (it.hasNext()) {
            columnInfo = (String[]) it.next();
            columnValue = columnInfo[0];//列值
            columnType = columnInfo[1];//列数据类型

            if ("VARCHAR2".equals(columnType) || "CHAR".equals(columnType)) {
                if (Objects.nonNull(columnValue)) {
                    valueSql.append("\'").append(columnValue).append("\'");
                } else {
                    valueSql.append(columnValue);
                }
            }

            if ("NUMBER".equals(columnType) || "CLOB".equals(columnType)) {
                valueSql.append(columnValue);
            }

            if ("DATE".equals(columnType)) {
                if (Objects.isNull(columnValue)) {
                    valueSql.append(columnValue);
                } else {
                    valueSql.append("TO_DATE(").append("'").append(columnValue).append("','yyyy-mm-dd HH24:mi:ss'").append(")");
                }
            }

            valueSql.append(",");
        }
        return valueSql.substring(0, valueSql.length() - 1);
    }

    /**
     * 根据表名构建创建触发器sql，监听表的更新操作
     *
     * @param tableName 表名称
     * @return 创建触发器sql
     */
    public static String buildCreateTriggerSql(String tableName) {
        if (null == tableName) {
            return null;
        }

        return " CREATE OR REPLACE TRIGGER SYNC_" + tableName.toUpperCase() + "_TRIGGER" +
                " AFTER UPDATE OR DELETE ON " + tableName +
                " FOR EACH ROW " +
                "DECLARE v_id NUMBER; " +
                "BEGIN " +
                " if updating then " +
                "v_id := :new.id;" +
                " elsif deleting then " +
                "v_id := :old.id;" +
                " end if; " +
                "INSERT INTO " + SqlUtil.UPDATE_ACTION_TABLENAME + " VALUES(sysdate,v_id,'" + tableName + "');" +
                "END;";
    }
}
